import pandas as pd

A = pd.read_csv('data/evaluation_public.csv', header=0)
B = pd.read_csv('data/sale_all_years.csv', header=0, index_col=0)
C = B.loc[:, ['province', 'model']]
k = 2
for year in ['2018']:
    for month in ['01', '02', '03', '04']:
        colname = year + month
        C.insert(k, colname, 0)
        k += 1

C.set_index(['province', 'model'], inplace=True)

for i in range(len(A)):
    year = A.loc[i, 'regYear']
    month = A.loc[i, 'regMonth']
    monstr = str(month) if month > 9 else '0' + str(month)
    colname = str(year) + monstr
    C.loc[(A.loc[i, 'province'], A.loc[i, 'model']), colname] = A.loc[i, 'id']

C.reset_index(inplace=True)
C.to_csv('data/sale_index.csv', index=True, header=True)